﻿CREATE PROCEDURE [dbo].[spUserApplications]
	 @UserID int
as
set nocount on

declare @UserList table ( UserID int )

declare @ct int
insert into @UserList values ( @UserID )
select @ct = COUNT(*) from @UserList
loopTop:

insert into @UserList 
select distinct T1.GroupID
from tblUserGroup T1
	join @UserList T2 on T1.UserID = T2.UserID
	left outer join @UserList T3 on T1.GroupID = T3.UserID
where T3.UserID is null

if @ct < ( select COUNT(*) from @UserList ) goto loopTop

select T3.*
	from @UserList T1
		join tblUserApplication T2 on T1.UserID = T2.UserID
		join tblApplication T3 on T2.ApplicationID = T3.ApplicationID
